package modelo;

import java.awt.Image;
import java.awt.image.BufferedImage;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.imageio.ImageIO;
import javax.swing.JOptionPane;

public class Conexion {

	private String bd = "dbbuttowsky";
	 private String login = "root";
	 private String password = "qwe12qwe";
//	public static String login;
//	public static String password;
	private String ip = "localhost";
	private String url = "jdbc:mysql://" + ip + "/" + bd;
	private Connection conn = null;

	public Conexion() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, login, password);
			if (conn != null) {
				System.out.println("Conexion realizada con exito a " + bd);
			}
		} catch (SQLException e) {
			System.out.println(e);
		} catch (ClassNotFoundException e) {
			System.out.println(e);
		}
	}

	public Connection getConnection() {
		return this.conn;
	}

	public Object[][] select(String table, String fields, String where) {
		int registros = 0;
		String colname[] = fields.split(",");

		// Consultas SQL
		String q = "SELECT " + fields + " FROM " + table;
		String q2 = "SELECT count(*) as total FROM " + table;
		if (where != null) {
			q += " WHERE " + where;
			q2 += " WHERE " + where;
		}
		try {
			PreparedStatement pstm = conn.prepareStatement(q2);
			ResultSet res = pstm.executeQuery();
			res.next();
			registros = res.getInt("total");
			res.close();
		} catch (SQLException e) {
			System.out.println(e);
		}

		// se crea una matriz con tantas filas y columnas que necesite
		Object[][] data = new String[registros][fields.split(",").length];
		// realizamos la consulta sql y llenamos los datos en la matriz "Object"
		try {
			PreparedStatement pstm = conn.prepareStatement(q);
			ResultSet res = pstm.executeQuery();
			int i = 0;
			while (res.next()) {
				for (int j = 0; j <= fields.split(",").length - 1; j++) {
					data[i][j] = res.getString(colname[j].trim());
				}
				i++;
			}
			res.close();
		} catch (SQLException e) {
			System.out.println(e);
		}
		return data;
	}

	public boolean insert(String table, String fields, String values) {
		boolean res = false;
		// Se arma la consulta
		String q = " INSERT INTO " + table + " ( " + fields + " ) VALUES ( "
				+ values + " ) ";
		// se ejecuta la consulta
		try {
			PreparedStatement pstm = conn.prepareStatement(q);
			pstm.execute();
			pstm.close();
			res = true;
		} catch (SQLException e) {
			System.out.println(e);
			JOptionPane.showMessageDialog(null, e, "Error",
					JOptionPane.ERROR_MESSAGE);
		}
		return res;
	}

	public void Update(String tabla, String columna, String valor,String condicion) {
		String u = " UPDATE " + tabla + " SET " + columna + "= '" + valor + "' where " + condicion;
		// se ejecuta la consulta
		try {
			PreparedStatement pstm = conn.prepareStatement(u);
			pstm.execute();
			pstm.close();
			JOptionPane.showMessageDialog(null, "Registro actualizado", "Aviso", JOptionPane.INFORMATION_MESSAGE);
		} catch (SQLException e) {
			System.out.println(e);
		}
	}

	public void desconectar() {
		conn = null;
		System.out.println("La conexion a la  base de datos " + bd
				+ " a terminado. PROBLEM?");
	}

	public void eliminar(String tabla, String condicion) {
		String d = " DELETE FROM " + tabla + " where " + condicion;
		// se ejecuta la consulta
		try {
			PreparedStatement pstm = conn.prepareStatement(d);
			pstm.executeUpdate();
			pstm.close();
		} catch (SQLException e) {
			System.out.println(e);
		}
	}
	
	
	public boolean ingresarItem(String txtItem, String txtColor, String txtLogo, String txtObs, 
			String txtStockInicial, String txtStockMinimo, String txtTalla, FileInputStream Imagen) {
		boolean x = false;
        String sql = "INSERT INTO inventario" + "(Item, Color ,Talla , Logo, Observacion, EnStock, StockMinimo, Imagen) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

        if (Imagen!=null) {
        	try {
                PreparedStatement pstm = conn.prepareStatement(sql);
                pstm.setString(1, txtItem);
                pstm.setString(2, txtColor);
                pstm.setString(3, txtTalla);
                pstm.setString(4, txtLogo);
                pstm.setString(5, txtObs);
                pstm.setString(6, txtStockInicial);
                pstm.setString(7, txtStockMinimo);
                pstm.setBinaryStream(8, Imagen);
                pstm.executeUpdate();
                JOptionPane.showMessageDialog(null, "Item ingresado");
                x=true;
            } catch (SQLException e) {
                System.out.println(e);
                JOptionPane.showMessageDialog(null, "Error : "+ e , "Aviso", JOptionPane.ERROR_MESSAGE);
            }
		} else {
			JOptionPane.showMessageDialog(null, "Ingrese una imagen para el Item", "Advertencia", JOptionPane.ERROR_MESSAGE);
			x=false;
			
		}
        return x;
    }
	
    public Image getImagen(String id) {

        String sql = "SELECT Imagen FROM inventario WHERE idItem = " + id;
        Image icon = null;
        InputStream input = null;

        try {

            PreparedStatement pstm = conn.prepareStatement(sql);
            ResultSet res = pstm.executeQuery();

            if (res.next()) {
                input = res.getBinaryStream(1);
                BufferedImage bi = ImageIO.read(input);
                icon = bi;
            }

        } catch (IOException | SQLException ex) {
            Logger.getLogger(Conexion.class.getName()).log(Level.SEVERE, null, ex);

        }

        return icon;

    }

}
